如何更新鏈接到多個表的 FK - 更新時的級聯 (How to update FK linked to multiple table - Cascade on Update)

問題描述

如何更新鏈接到多個表的 FK ‑ 更新時的級聯 (How to update FK linked to multiple table ‑ Cascade on Update)

正如@pankaj 所指出的，如何克服 </p>

如果 ON UPDATE CASCADE 遞歸更新它先前在級聯期間更新的同一個表，它的行為就像 RESTRICT。這意味著您不能使用自引用的 ON UPDATE CASCADE 操作。這是為了防止級聯更新導致無限循環。

編輯 2：

1452: Cannot add or update a child row: a foreign key constraint fails
(`task`, CONSTRAINT `task_ibfk_1` FOREIGN KEY (`officeid`, `clientid`) REFERENCES `client` (`officeid`, `clientno`) ON UPDATE CASCADE)

僅供參考：我在 mariadb 10.3 以及mysql 8.0

參考解法

方法 1:

The problem is related to the way relationships are declared.

First of all, as commented by @Nick, there is no need for a relation between task and client, as this is already covered by the relation to compliance. Commenting the declaration of this superfluous constraint is enough the make the error disappear, as you can see in this db fiddle.

create table task
(
  officeid        char(6)                      not null,
  ...
  primary key (officeid, taskno),
  ‑‑ constraint task_ibfk_1
   ‑‑ foreign key (officeid, clientid) references client (officeid, clientno)
   ‑‑ on update cascade,
  constraint task_ibfk_4
  foreign key (officeid, clientid, complianceid) references compliance (officeid,     clientid, id)
    on update cascade
); 

Another suggestion is to use an autoincremented primary key in all tables (you can use an UNIQUE index to enforce composite referential integrity rules). This is the most usual way to proceed with MySQL, with which handling relationships is pretty straighforward.

方法 2:

I think that your problem stems from using mutable fields as primary keys

You can mitigate this by using a surrogate immutable primary key and adding a unique key to your mutable fields. You should be able to apply the same constraints as before without compromising data integrity

For example:

CREATE TABLE client (
  id INT(10) UNSIGNED NOT NULL AUTO‑INCREMENT PRIMARY,
  officeid CHAR(6) NOT NULL,
  clientno CHAR(10) NOT NULL,
  fname VARCHAR(40) NOT NULL
);

CREATE UNIQUE INDEX uq‑client‑officeid‑clientno IN client (officeid, clientno);

CREATE TABLE compliance (
  id SMALLINT(5) UNSIGNED NOT NULL AUTO‑INCREMENT PRIMARY,
  client_id INT(10) UNSIGNED NOT NULL,
  CONSTRAINT fk‑compliance‑client‑id FOREIGN KEY id 
    REFERENCES client (id)
);

CREATE INDEX ix‑compliance‑id‑client_id IN compliance (id, client_id);

CREATE TABLE task (
  id INT(10) UNSIGNED NOT NULL AUTO‑INCREMENT PRIMARY,
  client_id INT(10) UNSIGNED NOT NULL,
  compliance_id SMALLINT(5) UNSIGNED NULL,
  taskno CHAR(10) NOT NULL,
  taskname VARCHAR(50) NOT NULL,
  CONSTRAINT fk‑task‑client‑id FOREIGN KEY id 
    REFERENCES client (id),
  CONSTRAINT fk‑task‑compliance‑id‑client_id FOREIGN KEY (compliance_id, client_id) 
    REFERENCES compliance (id, client_id)
);

This table structure mimics your current constraints and will allow you to update a clientno without needing the cascades

Note the foreign key fk‑task‑compliance‑id‑client_id which makes sure the compliance referenced by a task contains the correct client_id

I would also consider a separate table, office, with a surrogate integer primary key and containing the character based officeid. This could then be reference by the client table

(by Adarsh Madrecha、GMB、Arth)

參考文件

1. How to update FK linked to multiple table ‑ Cascade on Update (CC BY‑SA 2.5/3.0/4.0)